import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  // 创建LLM配置表
  await knex.schema.createTable('llm_configurations', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(lower(hex(randomblob(4))) || \'-\' || lower(hex(randomblob(2))) || \'-4\' || substr(lower(hex(randomblob(2))),2) || \'-\' || substr(\'89ab\',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || \'-\' || lower(hex(randomblob(6))))'));
    table.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.enum('provider', ['qwen', 'deepseek', 'private']).notNullable();
    table.string('api_key_encrypted', 1000).notNullable().comment('加密的API密钥');
    table.string('endpoint_url', 500).nullable().comment('私有部署端点URL');
    table.json('model_config').nullable().comment('模型配置参数');
    table.boolean('is_active').defaultTo(true);
    table.timestamp('last_tested_at').nullable();
    table.boolean('connection_status').defaultTo(false);
    table.timestamps(true, true);
    
    // 索引
    table.index(['user_id']);
    table.index(['provider']);
    table.index(['is_active']);
  });

  // 创建AI分析结果表
  await knex.schema.createTable('analysis_results', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(lower(hex(randomblob(4))) || \'-\' || lower(hex(randomblob(2))) || \'-4\' || substr(lower(hex(randomblob(2))),2) || \'-\' || substr(\'89ab\',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || \'-\' || lower(hex(randomblob(6))))'));
    table.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.enum('analysis_type', ['sleep', 'stress', 'exercise', 'general', 'nutrition']).notNullable();
    table.date('analysis_date').notNullable().comment('分析数据的日期');
    table.json('input_data').notNullable().comment('输入的健康数据');
    table.text('analysis_content').notNullable().comment('AI分析内容');
    table.json('recommendations').nullable().comment('建议列表');
    table.integer('health_score').nullable().comment('健康评分 0-100');
    table.json('metrics').nullable().comment('各项指标评分');
    table.string('llm_provider', 20).notNullable().comment('使用的LLM提供商');
    table.string('model_version', 50).nullable().comment('模型版本');
    table.integer('processing_time_ms').nullable().comment('处理时间(毫秒)');
    table.enum('status', ['pending', 'completed', 'failed']).defaultTo('pending');
    table.text('error_message').nullable();
    table.timestamps(true, true);
    
    // 索引
    table.index(['user_id']);
    table.index(['analysis_type']);
    table.index(['analysis_date']);
    table.index(['status']);
    table.index(['health_score']);
  });

  // 创建健康目标表
  await knex.schema.createTable('health_goals', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(lower(hex(randomblob(4))) || \'-\' || lower(hex(randomblob(2))) || \'-4\' || substr(lower(hex(randomblob(2))),2) || \'-\' || substr(\'89ab\',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || \'-\' || lower(hex(randomblob(6))))'));
    table.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.enum('goal_type', ['steps', 'sleep', 'weight', 'exercise', 'heart_rate']).notNullable();
    table.string('goal_name', 100).notNullable();
    table.text('description').nullable();
    table.json('target_values').notNullable().comment('目标值配置');
    table.date('start_date').notNullable();
    table.date('end_date').nullable();
    table.enum('frequency', ['daily', 'weekly', 'monthly']).defaultTo('daily');
    table.enum('status', ['active', 'completed', 'paused', 'cancelled']).defaultTo('active');
    table.decimal('progress_percentage', 5, 2).defaultTo(0);
    table.json('milestones').nullable().comment('里程碑记录');
    table.timestamp('last_updated_at').nullable();
    table.timestamps(true, true);
    
    // 索引
    table.index(['user_id']);
    table.index(['goal_type']);
    table.index(['status']);
    table.index(['start_date']);
  });

  // 创建数据同步记录表
  await knex.schema.createTable('sync_records', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(lower(hex(randomblob(4))) || \'-\' || lower(hex(randomblob(2))) || \'-4\' || substr(lower(hex(randomblob(2))),2) || \'-\' || substr(\'89ab\',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || \'-\' || lower(hex(randomblob(6))))'));
    table.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.string('data_source', 50).notNullable().comment('数据源');
    table.enum('sync_type', ['full', 'incremental']).notNullable();
    table.timestamp('sync_start_time').notNullable();
    table.timestamp('sync_end_time').nullable();
    table.enum('status', ['in_progress', 'completed', 'failed']).notNullable();
    table.integer('records_processed').defaultTo(0);
    table.integer('records_success').defaultTo(0);
    table.integer('records_failed').defaultTo(0);
    table.text('error_details').nullable();
    table.json('sync_summary').nullable().comment('同步摘要信息');
    table.timestamps(true, true);
    
    // 索引
    table.index(['user_id']);
    table.index(['data_source']);
    table.index(['status']);
    table.index(['sync_start_time']);
  });

  // 创建系统日志表
  await knex.schema.createTable('system_logs', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(lower(hex(randomblob(4))) || \'-\' || lower(hex(randomblob(2))) || \'-4\' || substr(lower(hex(randomblob(2))),2) || \'-\' || substr(\'89ab\',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || \'-\' || lower(hex(randomblob(6))))'));
    table.uuid('user_id').nullable().references('id').inTable('users').onDelete('SET NULL');
    table.enum('log_level', ['debug', 'info', 'warn', 'error']).notNullable();
    table.string('category', 50).notNullable().comment('日志分类');
    table.string('message', 1000).notNullable();
    table.json('metadata').nullable().comment('额外的日志元数据');
    table.string('request_id', 100).nullable();
    table.string('session_id', 100).nullable();
    table.string('ip_address', 45).nullable();
    table.string('user_agent', 500).nullable();
    table.timestamps(true, true);
    
    // 索引
    table.index(['log_level']);
    table.index(['category']);
    table.index(['user_id']);
    table.index(['created_at']);
    table.index(['request_id']);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('system_logs');
  await knex.schema.dropTableIfExists('sync_records');
  await knex.schema.dropTableIfExists('health_goals');
  await knex.schema.dropTableIfExists('analysis_results');
  await knex.schema.dropTableIfExists('llm_configurations');
}